﻿using NfsCommon;
using NfsCommon.DbHelper;
using NfsModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NfsDAL
{
    /// <summary>
    /// 数据访问类:shenqing
    /// </summary>
    public partial class ShenqingDAL
    {
        public ShenqingDAL()
        { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperMySQL.GetMaxID("id", "t_shenqing");
        }

        public int GetIdentityID(string tableName)
        {
            return DbHelperMySQL.GetIdentityID(tableName);

        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_shenqing");
            strSql.Append(" where id=" + id + " ");
            return DbHelperMySQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(NfsModel.shenqing model, out int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.account_id != null)
            {
                strSql1.Append("account_id,");
                strSql2.Append("" + model.account_id + ",");
            }
            if (model.parents_id != null)
            {
                strSql1.Append("parents_id,");
                strSql2.Append("" + model.parents_id + ",");
            }
            if (model.shState != null)
            {
                strSql1.Append("shState,");
                strSql2.Append("" + model.shState + ",");
            }
            if (model.category_id != null)
            {
                strSql1.Append("category_id,");
                strSql2.Append("" + model.category_id + ",");
            }
            if (model.level_id != null)
            {
                strSql1.Append("level_id,");
                strSql2.Append("" + model.level_id + ",");
            }
            if (model.name != null)
            {
                strSql1.Append("name,");
                strSql2.Append("'" + model.name + "',");
            }
            if (model.company_name != null)
            {
                strSql1.Append("company_name,");
                strSql2.Append("'" + model.company_name + "',");
            }
            if (model.level_name != null)
            {
                strSql1.Append("level_name,");
                strSql2.Append("'" + model.level_name + "',");
            }
            if (model.mobile != null)
            {
                strSql1.Append("mobile,");
                strSql2.Append("'" + model.mobile + "',");
            }
            if (model.contract_name != null)
            {
                strSql1.Append("contract_name,");
                strSql2.Append("'" + model.contract_name + "',");
            }
            if (model.qq != null)
            {
                strSql1.Append("qq,");
                strSql2.Append("'" + model.qq + "',");
            }
            if (model.wx_openid != null)
            {
                strSql1.Append("wx_openid,");
                strSql2.Append("'" + model.wx_openid + "',");
            }
            if (model.ww_code != null)
            {
                strSql1.Append("ww_code,");
                strSql2.Append("'" + model.ww_code + "',");
            }
            if (model.id_card != null)
            {
                strSql1.Append("id_card,");
                strSql2.Append("'" + model.id_card + "',");
            }
            if (model.shop_url != null)
            {
                strSql1.Append("shop_url,");
                strSql2.Append("'" + model.shop_url + "',");
            }
            if (model.valid != null)
            {
                strSql1.Append("valid,");
                strSql2.Append("" + model.valid + ",");
            }
            if (model.state != null)
            {
                strSql1.Append("state,");
                strSql2.Append("" + model.state + ",");
            }
            if (model.start_time != null)
            {
                strSql1.Append("start_time,");
                strSql2.Append("'" + model.start_time + "',");
            }
            if (model.end_time != null)
            {
                strSql1.Append("end_time,");
                strSql2.Append("'" + model.end_time + "',");
            }
            if (model.sq_img != null)
            {
                strSql1.Append("sq_img,");
                strSql2.Append("'" + model.sq_img + "',");
            }
            if (model.au_code != null)
            {
                strSql1.Append("au_code,");
                strSql2.Append("'" + model.au_code + "',");
            }
            strSql.Append("insert into t_shenqing(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                id = GetIdentityID("t_shenqing");
                return true;
            }
            else
            {
                id = -1;
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(NfsModel.shenqing model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_shenqing set ");
            if (model.account_id != null)
            {
                strSql.Append("account_id=" + model.account_id + ",");
            }
         
            if (model.parents_id != null)
            {
                strSql.Append("parents_id=" + model.parents_id + ",");
            }
            if (model.shState != null)
            {
                strSql.Append("shState=" + model.shState + ",");
            }
           
            if (model.category_id != null)
            {
                strSql.Append("category_id=" + model.category_id + ",");
            }
          
            if (model.level_id != null)
            {
                strSql.Append("level_id=" + model.level_id + ",");
            }
          
            if (model.name != null)
            {
                strSql.Append("name='" + model.name + "',");
            }
           
            if (model.company_name != null)
            {
                strSql.Append("company_name='" + model.company_name + "',");
            }
            
            if (model.level_name != null)
            {
                strSql.Append("level_name='" + model.level_name + "',");
            }
           
            if (model.mobile != null)
            {
                strSql.Append("mobile='" + model.mobile + "',");
            }
           
            if (model.contract_name != null)
            {
                strSql.Append("contract_name='" + model.contract_name + "',");
            }
           
            if (model.qq != null)
            {
                strSql.Append("qq='" + model.qq + "',");
            }
           
            if (model.wx_openid != null)
            {
                strSql.Append("wx_openid='" + model.wx_openid + "',");
            }
           
            if (model.ww_code != null)
            {
                strSql.Append("ww_code='" + model.ww_code + "',");
            }
            
            if (model.id_card != null)
            {
                strSql.Append("id_card='" + model.id_card + "',");
            }
            
            if (model.shop_url != null)
            {
                strSql.Append("shop_url='" + model.shop_url + "',");
            }
           
            if (model.valid != null)
            {
                strSql.Append("valid=" + model.valid + ",");
            }
           
            if (model.state != null)
            {
                strSql.Append("state=" + model.state + ",");
            }
           
            if (model.start_time != null)
            {
                strSql.Append("start_time='" + model.start_time + "',");
            }
           
            if (model.end_time != null)
            {
                strSql.Append("end_time='" + model.end_time + "',");
            }
            
            if (model.sq_img != null)
            {
                strSql.Append("sq_img='" + model.sq_img + "',");
            }
            if (model.au_code != null)
            {
                strSql.Append("au_code='" + model.au_code + "',");
            }

            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where id=" + model.id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_shenqing ");
            strSql.Append(" where id=" + id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }		/// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_shenqing ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.shenqing GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  ");
            strSql.Append(" id,account_id,parents_id,category_id,shState,level_id,name,company_name,level_name,mobile,contract_name,qq,wx_openid,ww_code,id_card,shop_url,valid,state,start_time,end_time,sq_img,au_code");
            strSql.Append(" from t_shenqing ");
            strSql.Append(" where id=" + id + "");
            NfsModel.agency model = new NfsModel.agency();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.shenqing DataRowToModel(DataRow row)
        {
            NfsModel.shenqing model = new NfsModel.shenqing();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["account_id"] != null && row["account_id"].ToString() != "")
                {
                    model.account_id = int.Parse(row["account_id"].ToString());
                }
                if (row["parents_id"] != null && row["parents_id"].ToString() != "")
                {
                    model.parents_id = int.Parse(row["parents_id"].ToString());
                }
                if (row["shState"] != null && row["shState"].ToString() != "")
                {
                    model.shState = int.Parse(row["shState"].ToString());
                }
                if (row["category_id"] != null && row["category_id"].ToString() != "")
                {
                    model.category_id = int.Parse(row["category_id"].ToString());
                }
                if (row["level_id"] != null && row["level_id"].ToString() != "")
                {
                    model.level_id = int.Parse(row["level_id"].ToString());
                }
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["company_name"] != null)
                {
                    model.company_name = row["company_name"].ToString();
                }
                if (row["level_name"] != null)
                {
                    model.level_name = row["level_name"].ToString();
                }
                if (row["mobile"] != null)
                {
                    model.mobile = row["mobile"].ToString();
                }
                if (row["contract_name"] != null)
                {
                    model.contract_name = row["contract_name"].ToString();
                }
                if (row["qq"] != null)
                {
                    model.qq = row["qq"].ToString();
                }
                if (row["wx_openid"] != null)
                {
                    model.wx_openid = row["wx_openid"].ToString();
                }
                if (row["ww_code"] != null)
                {
                    model.ww_code = row["ww_code"].ToString();
                }
                if (row["id_card"] != null)
                {
                    model.id_card = row["id_card"].ToString();
                }
                if (row["shop_url"] != null)
                {
                    model.shop_url = row["shop_url"].ToString();
                }
                if (row["valid"] != null && row["valid"].ToString() != "")
                {
                    model.valid = int.Parse(row["valid"].ToString());
                }
                if (row["state"] != null && row["state"].ToString() != "")
                {
                    model.state = int.Parse(row["state"].ToString());
                }
                if (row["start_time"] != null && row["start_time"].ToString() != "")
                {
                    model.start_time = DateTime.Parse(row["start_time"].ToString());
                }
                if (row["end_time"] != null && row["end_time"].ToString() != "")
                {
                    model.end_time = DateTime.Parse(row["end_time"].ToString());
                }
                if (row["sq_img"] != null)
                {
                    model.sq_img = row["sq_img"].ToString();
                }
                if (row["au_code"] != null)
                {
                    model.au_code = row["au_code"].ToString();
                }

            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,account_id,parents_id,shState,category_id,level_id,name,company_name,level_name,mobile,contract_name,qq,wx_openid,ww_code,id_card,shop_url,valid,state,start_time,end_time,sq_img,au_code");
            strSql.Append(" FROM t_shenqing ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_shenqing ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperMySQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from t_shenqing T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /*
        */

        #endregion  Method
        #region  自定义方法

     

 

        #region PC模糊查询
        public DataTable GetAgencyBycondtion(int pageindex, int pagesize)
        {
            int startindex = (pageindex - 1) * pagesize;
            string strSql = string.Format(@"select top " + pagesize + " * from (select row_number() over(order by ag.id desc) as rownumber,ag.*,ca.name as categoryName,le.name as levelName,CASE  WHEN ag.state=0 THEN '待审核' WHEN  ag.state=1 THEN '已审核' END as states from t_shenqing as ag left join t_category as ca on ag.category_id=ca.id left join t_level as le on ag.level_id=le.id where ag.valid=1) as a where 1=1 ");
            strSql += " and a.rownumber>" + startindex+ " ";
            return DbHelperMySQL.Query(strSql).Tables[0];
        }
        public object GetAgencyBycondtionCount()
        {
            string strSql = string.Format(@"select count(*) from (select  ag.*,ca.name as categoryName,le.name as levelName,
  CASE 
           
        WHEN ag.state=0 THEN '待审核'
       WHEN  ag.state=1 THEN '已审核'
       END as states
from t_shenqing as ag
left join t_category as ca on ag.category_id=ca.id
left join t_level as le on ag.level_id=le.id where ag.valid=1) as a where 1=1 ");
           
             
            return DbHelperMySQL.GetSingle(strSql);
        }
        #endregion       
        #endregion  MethodEx
    }
}
